﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Collections;

namespace Cashier.utils
{
    public class OfficeUtil
    {
        /// <summary>
        /// DataGridView导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="isSelectedRows">是否选择数据</param>
        /// <param name="savePath">保存路径</param>
        public static void ExportExcel(DataGridView dgv, bool isSelectedRows, String savePath)
        {
            try
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                ApplicationClass excel = new ApplicationClass();
                excel.Application.Workbooks.Add(true);
                excel.Visible = false;
                if (excel == null)
                {
                    MessageBox.Show("Excel无法启动！");
                    return;
                }

                Workbooks books = excel.Workbooks;
                Workbook book = books.Add(miss);
                Worksheet sheet = (Worksheet)book.Sheets[1];
                sheet.Name = "sheet1";
                ArrayList columnList = new ArrayList();
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    if (dgv.Columns[i].Visible)
                    {
                        sheet.Cells[1, columnList.Count + 1] = dgv.Columns[i].HeaderText;
                        columnList.Add(i);
                    }
                }

                if (isSelectedRows)
                {
                    for (int i = 0; i < dgv.SelectedRows.Count; i++)
                    {
                        for (int j = 0; j < columnList.Count; j++)
                        {
                            sheet.Cells[i + 2, j + 1] = " " + dgv.SelectedRows[i].Cells[(int)columnList[j]].Value.ToString();
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < dgv.Rows.Count; i++)
                    {
                        for (int j = 0; j < columnList.Count; j++)
                        {
                            sheet.Cells[i + 2, j + 1] = " " + dgv.Rows[i].Cells[(int)columnList[j]].Value.ToString();
                        }
                    }
                }

                sheet.SaveAs(savePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                book.Close(false, miss, miss);
                books.Close();
                excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 报表生成Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        /// <param name="sheet1Name"></param>
        /// <param name="sheet2Name"></param>
        /// <param name="imagePath"></param>
        /// <param name="imageWidth"></param>
        /// <param name="imageHeight"></param>
        public static void ExportExcel(DataGridView dgv, String filePath, String sheet1Name, String sheet2Name, String imagePath,
            float imageWidth, float imageHeight)
        {
            try
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                ApplicationClass excel = new ApplicationClass();
                excel.Application.Workbooks.Add(true);
                excel.Visible = false;
                if (excel == null)
                {
                    MessageBox.Show("Excel无法启动！");
                    return;
                }

                Workbooks books = excel.Workbooks;
                Workbook book = books.Add(miss);
                Worksheet sheet = (Worksheet)book.Sheets[1];
                sheet.Name = sheet1Name;
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    sheet.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
                }

                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    for (int j = 0; j < dgv.Columns.Count; j++)
                    {
                        sheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
                    }
                }
                
                sheet = (Worksheet)book.Sheets[2];
                sheet.Name = sheet2Name;
                sheet.Shapes.AddPicture(imagePath, Microsoft.Office.Core.MsoTriState.msoFalse,
                    Microsoft.Office.Core.MsoTriState.msoTrue, 0, 0, imageWidth, imageHeight);

                sheet.SaveAs(filePath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                book.Close(false, miss, miss);
                books.Close();
                excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
